package com.bdqn.dao.Impl;

import com.bdqn.dao.AdminQueryDao;
import com.bdqn.entity.CheckWork;
import com.bdqn.entity.Employee;
import com.bdqn.util.JDBCUtils;
import com.mysql.cj.x.protobuf.MysqlxDatatypes;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class AdminQueryDaoImpl implements AdminQueryDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public int findByAll() {
        String sql = "select count(*) from checkwork";
        return template.queryForObject(sql,Integer.class);
    }

    @Override
    public int insert(String empId, String date) {
        String sql = "insert into checkwork(empId,date,absenteeism) values(?,?,?)";
        return template.update(sql,empId,date,1);
    }

    @Override
    public int finByIdAndDate(String empId, String date) {
        int count = 0;
        String sql = "select count(*) from checkwork where empId=? and date=?";
        try {
            count =  template.queryForObject(sql,Integer.class,empId,date);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public List<Employee> findEmp() {
        String sql = "select * from employee ";
        return template.query(sql,new BeanPropertyRowMapper<Employee>(Employee.class));
    }

    @Override
    public List<CheckWork> FindPageQueryByCond(int offset, int pageSize, String empId, int depId, String start, String end) {
        List<CheckWork> list = null;
        StringBuilder sql = new StringBuilder("select depname,c.empId,name,date,starttime,endtime,latecome,absenteeism,leave_early from checkwork c inner join employee e on  c.empId = e.empId inner join department d on e.depId = d.depId ");
        List<Object> params = new ArrayList<>();
        if(empId!=null && !"null".equals(empId) && empId.length()>0){
            sql.append(" and e.empId like ? ");
            params.add("%"+empId+"%");
        }
        if(depId>0){
            sql.append(" and d.depId=? ");
            params.add(depId);
        }
        if(start!=null && !"null".equals(start) && start.length()>0 && end!=null && !"null".equals(end) && end.length()>0){
            sql.append(" and c.date between ? and ?");
            params.add(start);
            params.add(end);
        }
        sql.append(" limit ?,? ");
        params.add(offset);
        params.add(pageSize);
        try {
            list = template.query(sql.toString(),new BeanPropertyRowMapper<CheckWork>(CheckWork.class),params.toArray());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int findByCondition(String empId, int depId, String start, String end) {
        int count = 0;
        StringBuilder sql = new StringBuilder("select count(*) from checkwork c inner join employee e on  c.empId = e.empId inner join department d on e.depId = d.depId ");
        List<Object> params = new ArrayList<>();
        if(empId!=null && !"null".equals(empId) && empId.length()>0){
            sql.append(" and e.empId like ? ");
            params.add("%"+empId+"%");
        }
        if(depId>0){
            sql.append(" and d.depId=? ");
            params.add(depId);
        }
        if(start!=null && !"null".equals(start) && start.length()>0 && end!=null && !"null".equals(end) && end.length()>0){
            sql.append(" and c.date between ? and ?");
            params.add(start);
            params.add(end);
        }
        try {
            count = template.queryForObject(sql.toString(),Integer.class,params.toArray());
        } catch (DataAccessException e) {

        }
        return count;
    }

    @Override
    public int findway(int week, int depId) {
        String sql = "select count(*) from way where week=? and depId=?";
        return template.queryForObject(sql,Integer.class,week,depId);
    }

    @Override
    public int updateway(int week, int depId, String start, String end) {
        String sql = "update way set start=? , end=? where week=? and depId=?";
        return template.update(sql,start,end,week,depId);
    }

    @Override
    public int addway(int week, int depId, String start, String end) {
        String sql = "insert into way(week,depId,start,end) values(?,?,?,?)";
        return template.update(sql,week,depId,start,end);
    }

    @Override
    public List<CheckWork> findByPage(int offset, int pageSize) {
        String sql = "select depname,c.empId,name,date,starttime,endtime,latecome,absenteeism,leave_early \n" +
                "from checkwork c inner join employee e on  c.empId = e.empId inner join department d on e.depId = d.depId limit ?,?";
        return template.query(sql,new BeanPropertyRowMapper<CheckWork>(CheckWork.class),offset,pageSize);
    }
}
